#!/usr/bin/python
import sys
import MySQLdb
import yeast_util

conn = MySQLdb.connect (host = "saskatoon",
                           user = "dudley_omics",
                           passwd = "dud1ey",
                           db = "omics")
cursor = conn.cursor ()

def executeInsert(key, chr, start, end, strand):
	try:
		insertSql = "INSERT INTO yeast_gene_annotations (systematic_name,common_name,chr,start,end,strand) VALUES ('%s','%s','%s',%i,%i,'%s');" %(key, key, chr, start, end, strand)
		cursor.execute(insertSql)
		print "Number of rows inserted: %d" % cursor.rowcount
	except MySQLdb.Error, e:
		print "Error %d: %s" % (e.args[0], e.args[1])

def executePositionUpdate(key, chr, start, end, strand):
        updateSql = "update yeast_gene_annotations set chr = '%s', start = %i, end = %i, strand='%s' where systematic_name = '%s'" %(chr, start, end, strand, key) 
	#print updateSql
        cursor.execute(updateSql)
        #print "updated: %d" % (cursor.rowcount)
	if (cursor.rowcount == 0):
		print "updated0:%s" % key
		executeInsert(key, chr, start, end, strand)

def executeSelect(gene, op):
	select = "select systematic_name, common_name from yeast_gene_annotations where systematic_name " +  op + " '" + gene + "'"
	print select
	#conn.query(select)
	cursor.execute(select)
	#	"SELECT * FROM yeast_gene_annotations WHERE systematic_name = '" + gene + "'")
	#cursor.fetch()
	#r = conn.store_result()
	rows = cursor.fetchall()
	print "Number of rows returned %d" % cursor.rowcount
	for row in rows:
		print "\t".join(row[0:len(row)])

def executeCGSSelect(key):
	cursor.execute("select gene, mod_seq, ctype, chrom, mod_start, mod_end from cgs_changes_classifications where cgs_key ='%s' and (gene is not null and gene != '') and mod_seq != ''" % key)
	rows = cursor.fetchall()
	return rows

def executeCGSUpdate(sql):
	try:
		cursor.execute(sql)
		#if (cursor.rowcount == 0):
		print "updated :%i %s" % (cursor.rowcount, sql)
	except MySQLdb.Error, e:
		print "Error %s \n %d: %s" % (sql, e.args[0], e.args[1])
			

def cleanup():
	cursor.close()
	conn.commit()
	conn.close()

if __name__ == "__main__":
	op = '='
	if (len(sys.argv) >= 3):
		op = sys.argv[2]
	#executeSelect(sys.argv[1], op)
	#executeUpdate('YAL001C','I',1,100,'+')
	cgs_key = sys.argv[1]
	rows = executeCGSSelect(cgs_key)
	for r in rows:
		#print "\t".join(r[0:len(r)])
		if (yeast_util.isAntisense(r[0]) != None):
			rcomp_seq = yeast_util.transComplement(r[1])
			#print 'updated %s and update sense for %s include ctype cgskey chr refstart' % (rcomp_seq, r[0])  
			updateSql = "update cgs_changes_classifications set strand = '-', mod_seq = '%s' where cgs_key = '%s' and gene= '%s' and ctype = '%s' and chrom = '%s' and mod_start = %s and mod_end = %s" %(rcomp_seq, cgs_key, r[0], r[2], r[3], r[4], r[5])
			executeCGSUpdate(updateSql)
	cleanup()
